import pymysql
from flask import current_app

from app import es
from apps.utils.tools import ValueGetKey

linkdicts = {"equal": "=", "like": "like"}

operators = {"and": "must", "or": "should", "not": "must_not"}


def deal_query(items):
    """
      link:
        type: string
        description: 连接符 and、or、not
        example: and
      feild_name:
        type: string
        description: 字段名
        example: 标题
      oparation:
        type: string
        description: equal、like
        example: like
      feild_value:
        type: string
        description:  前后两端模糊查询
        example: '长河坝'
    处理上传上来的条件，搜索条件的标准格式
    :param items:
    :return:
    """
    link = items.get("link").strip()
    feildName = items.get("feild_name").strip()
    oparation = items.get("oparation").strip()
    feildValue = items.get("feild_value").strip()
    # 要求值小写20191220
    feildValue = feildValue.lower()
    # 20200922 考虑到es不需要两个id  keyid 与 _id重复 所以使用_id搜索 需要大写
    if feildName == "维普ID":
        feildValue = feildValue.upper()
    return link, feildName, oparation, feildValue


def create_sqlwhere(fieldsdicts, feildName, oparation, feildValue):
    """
    json上传的数据转换为 sql
    :param fieldsdicts:
    :param feildName:
    :param oparation:
    :param feildValue:
    :return:
    """
    # 通过值获取键
    feildkeys = ValueGetKey(fieldsdicts, feildName)
    return convert_sql(list(feildkeys), oparation, feildValue)


def convert_sql(feildkeys: list, oparation, feildValue):
    """
    将描述语言转换为对应的sql语句
    :param feildkeys: 一个list 多个键对应同一个值的情况
    :param oparation: =  or like
    :param feildValue: 值， oparation为like是 %{}%
    :return:
    """
    # like 需要前后模糊
    if oparation == "like":
        feildValue = "%{}%".format(feildValue)
    # 字段只对应一个key时
    if len(feildkeys) == 1:
        return "%s %s '%s'" % (feildkeys[0], oparation, feildValue)
    # 对应多个key 一般两个要用or连接然后使用括号包裹
    elif len(feildkeys) > 1:  # 中文对应多个值的情况
        temp_string = ""
        for feildName in feildkeys:
            temp_string = temp_string + "%s %s '%s' or " % (feildName, oparation, feildValue)
        temp_string = "(" + temp_string.rstrip(" or ") + ")"
        return temp_string
    else:
        return None


def convert_dsl(feildkeys: list, oparation, feildValue, filedstypedicts):
    """
    将描述语言转换为对应的dsl语句
    :param feildkeys: 一个list 多个键对应同一个值的情况
    :param oparation: =  or like
    :param feildValue: 值， oparation为like是 %{}%
    :return:
    """
    dicts = {}
    # like 使用match
    if oparation == "like":
        query = "match"
    else:
        query = "term"
    # 字段只对应一个key时
    if len(feildkeys) == 1:
        feildName = feildkeys[0]
        if query == "term" and filedstypedicts[feildName] == 2:
            feildName = feildName + ".keyword"
        ################################################
        if query == "match":
            dicts[query] = {feildName: {"query": feildValue, "fuzziness": "1"}}
        else:
            dicts[query] = {feildName: feildValue}
        ################################################上面替换下面，20211207，甘若迅要求，目的提高精确度
        # dicts[query] = {feildName: feildValue}
        ################################################
    # 对应多个key 一般两个要用or连接然后使用括号包裹
    elif len(feildkeys) > 1:  # 中文对应多个值的情况
        dicts["bool"] = {"should": []}
        for feildName in feildkeys:
            dicts_or = {}
            if query == "term" and filedstypedicts[feildName] == 2:
                feildName = feildName + ".keyword"
            ################################################
            if query == "match":
                dicts_or[query] = {feildName: {"query": feildValue, "fuzziness": "1"}}
            else:
                dicts_or[query] = {feildName: feildValue}
            ################################################上面替换下面，20211207，甘若迅要求，目的提高精确度
            # dicts_or[query] = {feildName: feildValue}
            ################################################
            dicts["bool"]["should"].append(dicts_or)
            if query == "match" and feildName in ("title", "title_alt"):
                # 如果是 title 加一个match_phrase提升准确度20210909
                dicts_or = {}
                dicts_or[query + "_phrase"] = {feildName: feildValue}
                dicts["bool"]["should"].append(dicts_or)
    else:
        return None
    return dicts


def order_sql(where, fieldsdicts):
    """
        按传入的顺序确定sql条件的优先级
        列表结构为
          link:
            type: string
            description: 连接符 and、or、not
            example: and
          feild_name:
            type: string
            description: 字段名
            example: 标题
          oparation:
            type: string
            description: equal、like
            example: like
          feild_value:
            type: string
            description:  前后两端模糊查询
            example: '长河坝'
    :param where:  传入的顺序优先级的列表
    :param fieldsdicts: sql用的id与中文的对比，id为键，一个中文描述可能对应多个键 一般为两个
    :return:
    """
    # 最后的sql表达式的string
    wherestring = ""
    i = 0
    # 上一个link的值
    previous = None
    # 获取一个结构
    for items in where:
        i += 1
        # 取出一个定义的结构
        link, feildName, oparation, feildValue = deal_query(items)
        # 值转移 防止注入
        feildValue = pymysql.escape_string(feildValue)
        # 连接符转换
        oparation = linkdicts[oparation]
        temp_string = create_sqlwhere(fieldsdicts, feildName, oparation, feildValue)
        if temp_string is None:
            return False, "%s 找不到对应的id" % feildName
        # 第一个值 除了not 其他符号可以不管 即 表示与其他条件and
        if i == 1:
            if link == 'not':
                wherestring = "not %s" % temp_string
            else:
                wherestring = temp_string
        else:
            # 上一个值没有 代表第二个
            if previous is None:
                if link == "not":
                    link = "and not"
                    # 设置当前link为上一个link
                    previous = "and"
                else:
                    previous = link
                # 直接按link拼接 无需括号
                wherestring = wherestring + " " + link + " " + temp_string
            else:
                # 当上一个为or 这一个 为 and 或者 not 时,需要对前面的加括号
                if previous == "or" and (link == "and" or link == "not"):
                    if link == "not":
                        link = "and not"
                    wherestring = "(" + wherestring + ")" + " " + link + " " + temp_string
                else:
                    # 或者不用加 因为 前面为and 后面为or 执行顺序也是一样的
                    if link == "not":
                        link = "and not"
                    wherestring = wherestring + " " + link + " " + temp_string
                # 如果为and not 设置 上一个为 and 否则link 是什么就是什么
                if link == "and not":
                    previous = "and"
                else:
                    previous = link
    # 返回最后组成的sql语句
    return True, wherestring


def sql_logic(where):
    """
    用中文表达逻辑
    :param where:
    :return:
    """
    wherestring = ""
    i = 0
    # 上一个link的值
    previous = None
    # 获取一个结构
    for items in where:
        i = i + 1
        # 取出一个定义的结构
        link, feildName, oparation, feildValue = deal_query(items)
        # 连接符转换
        oparation = linkdicts[oparation]
        temp_string = convert_sql([feildName], oparation, feildValue)
        # 第一个值 除了not 其他符号可以不管 即 表示与其他条件and
        if i == 1:
            if link == 'not':
                wherestring = "(not %s)" % temp_string
            else:
                wherestring = temp_string
        else:
            # 上一个值没有 代表第二个
            if previous is None:
                if link == "not":
                    link = "and not"
                    # 设置当前link为上一个link
                    previous = "and"
                    wherestring = wherestring + " and (not " + temp_string + ")"
                else:
                    previous = link
                    # 直接按link拼接 无需括号
                    wherestring = wherestring + " " + link + " " + temp_string
            else:
                # 当上一个为or 这一个 为 and 或者 not 时,需要对前面的加括号
                if previous == "or" and (link == "and" or link == "not"):
                    if link == "not":
                        link = "and not"
                        wherestring = "(" + wherestring + ")" + " and (not " + temp_string + ")"
                    else:
                        wherestring = "(" + wherestring + ")" + " " + link + " " + temp_string
                else:
                    # 或者不用加 因为 前面为and 后面为or 执行顺序也是一样的
                    if link == "not":
                        link = "and not"
                        wherestring = wherestring + " and (not " + temp_string + ")"
                    else:
                        wherestring = wherestring + " " + link + " " + temp_string
                # 如果为and not 设置 上一个为 and 否则link 是什么就是什么
                if link == "and not":
                    previous = "and"
                else:
                    previous = link
    return wherestring


def order_sql_logic(where, fieldsdicts):
    """
    用中文表达逻辑,并且返回sql语句
    :param where:
    :return:
    """
    wherestring = ""
    wheresql = ""
    i = 0
    # 上一个link的值
    previous = None
    # 获取一个结构
    for items in where:
        i += 1
        # 取出一个定义的结构
        link, feildName, oparation, feildValue = deal_query(items)
        # 值转移 防止注入
        feildValue = pymysql.escape_string(feildValue)
        # 连接符转换
        oparation = linkdicts[oparation]
        # 通过值获取键
        feildkeys = ValueGetKey(fieldsdicts, feildName)
        temp_sql = convert_sql(list(feildkeys), oparation, feildValue)
        temp_string = convert_sql([feildName], oparation, feildValue)
        temp_string = "({})".format(temp_string)
        if temp_sql is None:
            return False, "%s 找不到对应的id" % feildName, ""
        # 第一个值 除了not 其他符号可以不管 即 表示与其他条件and
        if i == 1:
            if link == 'not':
                wherestring = "(not %s)" % temp_string
                wheresql = "not %s" % temp_sql
            else:
                wherestring = temp_string
                wheresql = temp_sql
        else:
            # 上一个值没有 代表第二个
            if previous is None:
                if link == "not":
                    link = "and not"
                    # 设置当前link为上一个link
                    previous = "and"
                else:
                    previous = link
                # 直接按link拼接 无需括号
                wherestring = wherestring + " " + link + " " + temp_string
                wheresql = wheresql + " " + link + " " + temp_sql
            else:
                # 当上一个为or 这一个 为 and 或者 not 时,需要对前面的加括号
                if previous == "or" and (link == "and" or link == "not"):
                    if link == "not":
                        link = "and not"
                    wherestring = "(" + wherestring + ")" + " " + link + " " + temp_string
                    wheresql = "(" + wheresql + ")" + " " + link + " " + temp_sql
                else:
                    # 或者不用加 因为 前面为and 后面为or 执行顺序也是一样的
                    if link == "not":
                        link = "and not"
                    wherestring = wherestring + " " + link + " " + temp_string
                    wheresql = wheresql + " " + link + " " + temp_sql
                # 如果为and not 设置 上一个为 and 否则link 是什么就是什么
                if link == "and not":
                    previous = "and"
                else:
                    previous = link
    # 返回最后组成的sql语句
    return True, wherestring, wheresql


def order_dsl_logic(where: list, fieldsdicts, filedstypedicts, dsl=None, i=1, previous=None, wherestring=""):
    """
    直接转换成dsl 和文字描述 不经过sql转换
    :param where:
    :param fieldsdicts:
    :return:
    """
    dicts = {}
    if not where:
        return dsl, wherestring
    items = where.pop(0)
    # 取出一个定义的结构
    link, feildName, oparation, feildValue = deal_query(items)
    # 连接符转换
    oparation = linkdicts[oparation]
    # 通过值获取键
    feildkeys = ValueGetKey(fieldsdicts, feildName)
    temp_string = convert_sql([feildName], oparation, feildValue)
    temp_string = "({})".format(temp_string)
    temp_dsl = convert_dsl(list(feildkeys), oparation, feildValue, filedstypedicts)
    if temp_dsl is None:
        return False, "%s 找不到对应的id" % feildName, ""
    if i == 1:
        if link == 'not':
            wherestring = "(not %s)" % temp_string
            dicts["bool"] = {"must_not": [temp_dsl]}
        else:
            wherestring = temp_string
            dicts = temp_dsl
    else:
        # 上一个连接符没有 代表第二个
        if previous is None:
            if link == "not":
                link = "and not"
                # 设置当前link为上一个link
                previous = "and"
                dicts["bool"] = {"must": [dsl, {"bool": {"must_not": [temp_dsl]}}]}
            else:
                previous = link
                dicts["bool"] = {operators[previous]: [dsl, temp_dsl]}
            # 直接按link拼接 无需括号
            wherestring = wherestring + " " + link + " " + temp_string
        else:
            if link == "not":
                link = "and not"
                nowlink = "and"
                temp_dsl = {"bool": {"must_not": [temp_dsl]}}
            else:
                nowlink = link
            if previous == nowlink:
                dsl["bool"][operators[previous]].append(temp_dsl)
                dicts = dsl
                wherestring = wherestring + " " + link + " " + temp_string
            else:
                dicts["bool"] = {operators[nowlink]: [dsl, temp_dsl]}
                wherestring = "(" + wherestring + ")" + " " + link + " " + temp_string
            if link == "and not":
                previous = "and"
            else:
                previous = link
    i = i + 1
    return order_dsl_logic(where, fieldsdicts, filedstypedicts, dicts, i, previous, wherestring)


def update_dbcode(dbcode, table):
    alias = '/_alias/{}'.format(table)
    results = es.transport.perform_request('GET', alias)
    i = len(dbcode)
    for key in results.keys():
        if key in list(dbcode.keys()):
            continue
        else:
            i = i + 1
            dbcode[key] = i
    current_app.config['ES_DBCODE'] = dbcode
    return dbcode
